package com.ztesoft.report.backstage.job;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.ztesoft.report.base.pojo.R;
import com.ztesoft.report.dao.ReportDataFileUploadDao;
import com.ztesoft.report.dao.UploadLogDao;
import com.ztesoft.report.entity.*;
import com.ztesoft.report.service.*;
import com.ztesoft.report.utils.AESUtil;
import com.ztesoft.report.utils.ExcelDataExport;
import com.ztesoft.report.utils.excel.ExcelUtils;
import com.ztesoft.report.utils.excel.FileUtils;
import com.ztesoft.report.utils.ftp.SftpClient;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.PropertySource;
import org.springframework.context.annotation.Scope;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Component;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 报表文件生成上传服务
 *
 * @author 陈俊锋[296954345@qq.com]
 * @date 2018/6/25
 */
@Slf4j
@Component
@Scope("prototype")
@PropertySource(value = "classpath:backStageJob.properties")
public class ReportDataFileUploadJob {
    @Autowired
    DataService dataService;
    @Autowired
    ReportService reportService;
    @Autowired
    ReportDataFileUploadDao reportDataFileUploadDao;
    @Autowired
    UploadLogDao uploadLogDao;
    @Autowired
    UploadLogService uploadLogService;
    @Autowired
    ServiceInterfaceService serviceInterfaceService;
    @Autowired
    FileUtils fileUtils;
    @Autowired
    TempUploadLogService tempUploadLogService;

    @Value("${com.ztesoft.createExcelPath}")
    private String createExcelPath;
    @Value("${com.ztesoft.downpath}")
    private String downLoadPath;
    //分页插入Excel的大小
    @Value("${com.ztesoft.intoExcelOnceRows}")
    private Integer pageSize;

    public void run(ServiceInterface serviceInterface) {
        log.info("开始报表数据上传服务");
        // 1.获取service_interface表的report_id 已获取
        String reportId = serviceInterface.getReportId();
        List<ReportDto> reports = new LinkedList<>();
        if (reportId.contains(",")) {
            String[] reportIds = reportId.split(",");
            for(int i = 0 ; i < reportIds.length ; i++) {
                ReportDto reportDto = reportDataFileUploadDao.selectReportById(reportIds[i]);
                reports.add(reportDto);
            }
        } else {
            ReportDto reportDto = reportDataFileUploadDao.selectReportById(reportId);
            reports.add(reportDto);
        }
        //上传路径必然是同一个，这个前台已经效验，这里取第一个的数据即可
        ReportUpload reportUpload = reportDataFileUploadDao.selectReportUploadById(reports.get(0).getReportUploadId());
        String fileName = "";
        //服务器存放文件路径
        String createPath = "";
        //账期
        String billingCycle = null;
        //上传路径
        String uploadPath = null;
        //初始化账期 和 上传路径
        SimpleDateFormat sm = new SimpleDateFormat("yyyyMM");
        billingCycle = sm.format(new Date());
        uploadPath = reportUpload.getUploadPath() + "/" + billingCycle;
        try {
            for (ReportDto reportDto:reports) {
                //文件名
                fileName = reportDto.getName() + ".xls";
                createPath = createExcelPath + "/" + fileName;
                // 3.根据service_interface表把status中00E更新为00F 传入的service_interface对象已经修改为00F
                // 4.根据report表的 Sql_text，meta_columns，header生成报表excel
                this.createExcel(reportDto, createExcelPath, fileName);
                //4.5 根据生成好的EXCEL添加下面的制表部门等数据
                this.hanldeExcel(reportDto, createPath, fileName);
            }
            //合并2个Excel成一个多Sheet页的Excel
            if (reports.size() > 1) {
                this.mergeExcel(reports,createExcelPath,reportId);
                createPath = createExcelPath + "/" +"合并报表"+reportId + ".xls";
                fileName = "合并报表"+reportId + ".xls";
            }
            //5.0 检查账期目录 账期格式201806
            SftpClient sftpClient = SftpClient.connect(reportUpload.getHost(), Integer.parseInt(reportUpload.getPort()),
                    reportUpload.getUsername(), AESUtil.decrypt(reportUpload.getPassword()), 20000, 20000);
            //目录不存在
            if (!sftpClient.isDirExist(uploadPath)) {
                sftpClient.createDir(uploadPath);
            }
            // 5.根据Report_upload_id找到上传配置，把刚刚生成的excel上传的服务器上
            sftpClient.put(createPath, uploadPath);
            sftpClient.disconnect();
            // 删除本地文件
            File file = new File(createPath);
            if (file.exists()) {
                if (file.isFile()) {
                    file.delete();
                }
            }
            // 6. 更新service_interface表status为00A
            serviceInterface.setStatus("00A");
            serviceInterface.setUpdateTime(new Date());
            serviceInterfaceService.updateServiceInterface(serviceInterface);
            uploadLogService.deleteSame(serviceInterface.getName(),billingCycle);//删除当月重复名字
            // 7.插入上传日志表
            UploadLog uploadLog = initUploadLog(reportId,reports.get(0).getCategoryId(), reportUpload, serviceInterface.getName(),
                    billingCycle, uploadPath, "00A", "");
            uploadLogService.insert(uploadLog);
        } catch (Exception e) {
            log.error("", e);
            serviceInterface.setStatus("00X");
            serviceInterface.setErrorMsg(e.toString());
            serviceInterface.setUpdateTime(new Date());
            serviceInterfaceService.updateServiceInterface(serviceInterface);
            // 插入上传日志表
            UploadLog uploadLog = initUploadLog(reportId,reports.get(0).getCategoryId(), reportUpload, serviceInterface.getName(),
                    billingCycle, uploadPath, "00X", e.toString());
            uploadLogService.insert(uploadLog);
        }
    }

    /**
     * 初始化上传日志对象
     *
     * @param reportUpload 报表上传配置对象
     * @param fileName     上传文件名
     * @param billingCycle 账期
     * @param uploadPath   上传路径
     * @param status       上传状态 00A成功 00X 失败
     * @param errorMsg     失败原因
     * @return
     */
    private UploadLog initUploadLog(String reportId,Integer cateGoryId, ReportUpload reportUpload, String fileName,
                                    String billingCycle, String uploadPath, String status, String errorMsg) {
        UploadLog uploadLog = new UploadLog();
        uploadLog.setReportName(fileName);
        uploadLog.setReportId(reportId);
        uploadLog.setReportCategoryId(cateGoryId);
        uploadLog.setCycle(billingCycle);
        uploadLog.setReportUploadId(reportUpload.getId());
        uploadLog.setUploadPath(uploadPath);
        uploadLog.setDownloadCount(0);
        uploadLog.setStatus(status);
        uploadLog.setErrorMsg(errorMsg);
        uploadLog.setCreateTime(new Date());
        uploadLog.setUpdateTime(new Date());
        return uploadLog;
    }

    /**
     * 在生成好的Excel里面添加模板
     *
     * @param createExcelPath
     * @param fileName
     * @throws Exception
     */
    private void hanldeExcel(ReportDto reportDto, String createExcelPath, String fileName) throws Exception {
        //解析出需要合并的列
        String header = reportDto.getMetaColumns();
        List<Map> headConfig = new LinkedList<>();
        List<Integer> cellNums = new ArrayList<>();
        JSONArray jsonArray = JSON.parseArray(header);
        for (Object obj : jsonArray) {
            Map isMergeCell = JSON.parseObject(obj.toString());
            headConfig.add(isMergeCell);
        }
        for (int i = 0; i < headConfig.size(); i++) {
            if ("true".equals(headConfig.get(i).get("isMergeCell"))) {
                //把对应的列号存入,从0开始,不需要加1,也是为了方便后面合并
                cellNums.add(i);
            }
        }
        //查询出尾部模板的路径，下面用于读取
        TempUploadLog tempUploadLog = tempUploadLogService.selectById(reportDto.getEndTemp());
        FileInputStream inStream = null;
        try {
            //已经生成的EXCEL
            inStream = new FileInputStream(new File(createExcelPath));
            //尾部模板EXCEL
            //InputStream inputStream = new ClassPathResource("excel/endtemp.xls").getInputStream();
            InputStream inputStream = new FileInputStream(new File(downLoadPath+"/"+tempUploadLog.getTempName()));
            //读取尾部模板
            Workbook endwb = WorkbookFactory.create(inputStream);
            Sheet endSheet = endwb.getSheetAt(0);
            Workbook wb = WorkbookFactory.create(inStream);
            int sheetCount = wb.getNumberOfSheets();
            for (int i = 0; i < sheetCount; i++) {
                Sheet sheet = wb.getSheetAt(i);
                //getLastRowNum()此方法如果最后一行是5，获取的数字是4
                Integer lastRowNum = sheet.getLastRowNum();
                if (cellNums != null && cellNums.size() > 0) {
                    //遍历列号的集合 取出需要合并的列号
                    for (Integer cellNum : cellNums) {
                        //这个就是合并单元格
                        //参数说明：1：开始行 2：结束行  3：开始列 4：结束列
                        //比如我要合并 第二行到第四行的    第六列到第八列     sheet.addMergedRegion(new CellRangeAddress(1,3,5,7));
                        sheet.addMergedRegion(new CellRangeAddress(3, lastRowNum - 1, cellNum, cellNum));
                    }
                }
                //获取最后一行并空2行开始
                for (Row endrow : endSheet) {
                    boolean nullFlag = true;
                    for (Cell c : endrow) {
                        if (c != null && c.toString() != "") {
                            nullFlag = false;
                        }
                    }
                    if (nullFlag) {
                        continue;
                    }
                    Row row = sheet.createRow(sheet.getLastRowNum() + 3);
                    ExcelUtils.copyRow((HSSFWorkbook) wb, (HSSFSheet) sheet, (HSSFSheet) sheet,(HSSFRow) endrow, (HSSFRow) row, true);
                }
                FileOutputStream os;
                os = new FileOutputStream(createExcelPath);
                wb.write(os);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (inStream != null) {
                    inStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

    }

    /**
     * 合并多个Excel成一个多Sheet页的Excel
     * @param reports
     * @param createExcelPath
     * @param reportIds
     */
    private void mergeExcel(List<ReportDto> reports,String createExcelPath,String reportIds){
        List<String> fileNameList = new LinkedList<>();
        for (ReportDto reportDto:reports) {
            fileNameList.add(reportDto.getName());
        }
        InputStream in = null;
        FileOutputStream fileOut = null;
        try {
            //将所有类型的尽调excel文件合并成一个excel文件
            HSSFWorkbook newExcelCreat = new HSSFWorkbook();
            for (String fromExcelName : fileNameList) {//遍历每个源excel文件，fileNameList为源文件的名称集合
                in = new FileInputStream(createExcelPath + "/" + fromExcelName +".xls");
                HSSFWorkbook fromExcel = new HSSFWorkbook(in);
                for (int i = 0; i < fromExcel.getNumberOfSheets(); i++) {//遍历每个sheet
                    HSSFSheet oldSheet = fromExcel.getSheetAt(i);
                    HSSFSheet newSheet = newExcelCreat.createSheet(oldSheet.getSheetName());
                    ExcelUtils.copySheet(newExcelCreat, oldSheet, newSheet);
                }
            }
            String allFileName = createExcelPath + "/" + "合并报表" + reportIds + ".xls";
            fileOut = new FileOutputStream(allFileName);
            newExcelCreat.write(fileOut);
            fileOut.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                in.close();
                fileOut.close();
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        //删除各个源文件
        for(String fromExcelName:fileNameList) {//遍历每个源excel文件
            File file=new File(createExcelPath+File.separator+fromExcelName);
            if(file.exists()){
                file.delete();
            }
        }
    }



    /**
     * 生成Excel
     *
     * @param reportDto       报表对象
     * @param createExcelPath 创建路径
     * @param fileName        文件名
     */
    private void createExcel(ReportDto reportDto, String createExcelPath, String fileName) throws Exception {
        String sheetName = reportDto.getName();
        File file = new File(createExcelPath);
        if (!file.exists()) {
            file.mkdirs();
        }
        String filePath = createExcelPath + "/" + fileName;
        List<String> headConfig = new ArrayList<>();
        String header = reportDto.getHeader();
        //meta是下面用来处理是否导出字段时候使用
        String meta = reportDto.getMetaColumns();
        JSONArray jsonArrayMeta = JSON.parseArray(meta);
        //创建一个集合，用来放所有字段的json串
        List<String> metaList = new ArrayList<>();
        for (Object obj:jsonArrayMeta) {
            metaList.add(obj.toString());
        }
        //使用此方法得到导出EXCEL时不需要使用的字段
        List<String> cancelExport = this.getCancelExcel(metaList);
        JSONArray jsonArray = JSON.parseArray(header);
        //判断如果有不需要使用的串，此集合必定大小不为0
        if (cancelExport == null || cancelExport.size() == 0) {
            for (Object obj : jsonArray) {
                headConfig.add(obj.toString());
            }
        } else {
            for (Object obj : jsonArray) {
                boolean flag = true;
                //头串是用"分公司={\"width\":\"12\",\"field\":\"A\"}" 等号 = 隔开，所以要先分出来
                String[] objStr = obj.toString().split("=");
                //把等号后面变成json对象，方便取出对应的字段，与前面我们不需要导出的字段做对比，
                //如果在不需要导出集合中找不到，那就正常放在headConfig里面
                JSONObject j = JSONObject.parseObject(objStr[1]);
                for (String cancel:cancelExport) {
                    if (cancel.toUpperCase().equals(j.get("field").toString().toUpperCase())){
                        flag = false;
                        break;
                    }
                }
                if (flag) {
                    headConfig.add(obj.toString());
                }
            }
        }
        int listCount = dataService.executeQuerySqlCount(reportDto.getDsId().toString(), reportDto.getSqlText());
        if (listCount > 0) {
            //生成表头
            R r = fileUtils.createExcelByPOIKit(fileName, sheetName, listCount, Map.class, filePath, headConfig,reportDto.getHeadTemp(),reportDto.getEndTemp(),new SimpleDateFormat("yyyyMMdd").format(new Date()));
            ExcelDataExport excelDataExport = (ExcelDataExport) r.get("data");
            //生成表体
            ExcelUtils excelUtils = excelDataExport.getExcelUtils();
            int rowNum = 0;
            for (int i = 0; ; i++) {
                List<Map> maps = dataService.executeQuerySqlByPage(reportDto.getDsId().toString(),
                        reportDto.getSqlText(), pageSize, i + 1);
                if (maps.size() < 1) {
                    break;
                }
                //通过sql查询数据返回map
                //将值转换
                reportService.valueParseByConvertType(reportDto, maps);
                excelUtils.addExcelRow(maps, excelDataExport.getExportItems(),
                        excelDataExport.getWorkbook(), rowNum, excelDataExport.getRow(), 0, 100000);
                rowNum += maps.size();
            }
            ExcelUtils.createExcel(excelDataExport.getmResponse(), excelDataExport.getHandler(),
                    excelDataExport.getSheetName(), excelDataExport.getWorkbook(), excelDataExport.getOut());
        } else {
            throw new NullPointerException("该报表无数据无法生成Excel");
        }
    }

    /**
     * 传入所有字段集合，返回不需要导出的字段
     * @param metaList
     * @return
     */
    public List<String> getCancelExcel(List<String> metaList) {
        List<String> cancelExcel = new ArrayList<>();
        for (String str:metaList) {
            JSONObject j = JSONObject.parseObject(str);
            String isExport = j.get("isExport").toString();
            if ("false".equals(isExport)) {
                cancelExcel.add(j.get("name").toString());
            }
        }
        return cancelExcel;
    }

/*    public static void main(String[] args) throws Exception{
        String header  = "[{\"name\":\"A\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"false\"},{\"name\":\"B\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"false\"},{\"name\":\"C\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"false\"},{\"name\":\"D\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"false\"},{\"name\":\"E\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"false\"},{\"name\":\"F\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"false\"},{\"name\":\"G\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"true\"},{\"name\":\"H\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"true\"},{\"name\":\"I\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"true\"},{\"name\":\"J\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"false\"},{\"name\":\"K\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"true\"},{\"name\":\"L\",\"convertType\":\"1\",\"convertValue\":\"\",\"isPercent\":\"false\",\"isMergeCell\":\"false\"}]";
        List<Map> headConfig = new LinkedList<>();
        List<Integer> cellNums = new ArrayList<>();
        JSONArray jsonArray = JSON.parseArray(header);
        for (Object obj : jsonArray) {
            System.out.println(obj.toString());
            Map isMergeCell = JSON.parseObject(obj.toString());
            headConfig.add(isMergeCell);
        }
        System.out.println(headConfig);
    }*/

}
